package com.wdb.modules.mnt.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.util.StringUtils;
import com.wdb.utils.CloseUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;

import javax.sql.DataSource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: wendaobai@qq.com
 * @date: 2024/9/4 星期三 10:43
 */
@Slf4j
public class SqlUtils {

  /**
   * 获取数据源
   *
   * @param jdbcUrl /
   * @param userName /
   * @param password /
   * @return DataSource
   */
  private static DataSource getDataSource(String jdbcUrl, String userName, String password) {
    DruidDataSource druidDataSource = new DruidDataSource();
    String className;
    try {
      className = DriverManager.getDriver(jdbcUrl.trim()).getClass().getName();
    } catch (SQLException e) {
      throw new RuntimeException("Get class name error: =" + jdbcUrl);
    }
    if (StringUtils.isEmpty(className)) {
      DataTypeEnum dataTypeEnum = DataTypeEnum.urlOf(jdbcUrl);
      if (null == dataTypeEnum) {
        throw new RuntimeException("Not supported data type: jdbcUrl=" + jdbcUrl);
      }
      druidDataSource.setDriverClassName(dataTypeEnum.getDriver());
    } else {
      druidDataSource.setDriverClassName(className);
    }


    druidDataSource.setUrl(jdbcUrl);
    druidDataSource.setUsername(userName);
    druidDataSource.setPassword(password);
    // 配置获取连接等待超时的时间
    druidDataSource.setMaxWait(3000);
    // 配置初始化大小、最小、最大
    druidDataSource.setInitialSize(1);
    druidDataSource.setMinIdle(1);
    druidDataSource.setMaxActive(1);

    // 如果链接出现异常则直接判定为失败而不是一直重试
    druidDataSource.setBreakAfterAcquireFailure(true);
    try {
      druidDataSource.init();
    } catch (SQLException e) {
      log.error("Exception during pool initialization", e);
      throw new RuntimeException(e.getMessage());
    }

    return druidDataSource;
  }

  private static Connection getConnection(String jdbcUrl, String userName, String password) {
    DataSource dataSource = getDataSource(jdbcUrl, userName, password);
    Connection connection = null;
    try {
      connection = dataSource.getConnection();
    } catch (Exception ignored) {}
    try {
      int timeOut = 5;
      if (null == connection || connection.isClosed() || !connection.isValid(timeOut)) {
        log.info("connection is closed or invalid, retry get connection!");
        connection = dataSource.getConnection();
      }
    } catch (Exception e) {
      log.error("create connection error, jdbcUrl: {}", jdbcUrl);
      throw new RuntimeException("create connection error, jdbcUrl: " + jdbcUrl);
    } finally {
      CloseUtil.close(connection);
    }
    return connection;
  }

  private static void releaseConnection(Connection connection) {
    if (null != connection) {
      try {
        connection.close();
      } catch (Exception e) {
        log.error(e.getMessage(),e);
        log.error("connection close error：" + e.getMessage());
      }
    }
  }

  public static boolean testConnection(String jdbcUrl, String userName, String password) {
    Connection connection = null;
    try {
      connection = getConnection(jdbcUrl, userName, password);
      if (null != connection) {
        return true;
      }
    } catch (Exception e) {
      log.info("Get connection failed:" + e.getMessage());
    } finally {
      releaseConnection(connection);
    }
    return false;
  }

  public static String executeFile(String jdbcUrl, String userName, String password, File sqlFile) {
    Connection connection = getConnection(jdbcUrl, userName, password);
    try {
      batchExecute(connection, readSqlList(sqlFile));
    } catch (Exception e) {
      log.error("sql脚本执行发生异常:{}",e.getMessage());
      return e.getMessage();
    }finally {
      releaseConnection(connection);
    }
    return "success";
  }


  /**
   * 批量执行sql
   * @param connection /
   * @param sqlList /
   */
  public static void batchExecute(Connection connection, List<String> sqlList) {
    Statement st = null;
    try {
      st = connection.createStatement();
      for (String sql : sqlList) {
        if (sql.endsWith(";")) {
          sql = sql.substring(0, sql.length() - 1);
        }
        st.addBatch(sql);
      }
      st.executeBatch();
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    } finally {
      CloseUtil.close(st);
    }
  }

  /**
   * 将文件中的SQL语句以分号为单位读取到列表中
   *
   * 此方法主要用于处理SQL脚本文件，将文件中每一条独立的SQL语句读取出来并存入列表
   * 它通过识别分号来判断SQL语句的边界，这样可以有效地分割和读取连续的SQL语句
   *
   * @param sqlFile SQL文件路径
   * @return 包含SQL语句的列表
   * @throws Exception 如果文件读取或解析过程中发生错误
   */
  private static List<String> readSqlList(File sqlFile) throws Exception {
    // 初始化SQL语句列表
    List<String> sqlList = Lists.newArrayList();
    // 用于拼接SQL语句的字符串构建器
    StringBuilder sb = new StringBuilder();

    // 使用try-with-resources确保文件读取后资源能自动关闭
    try (BufferedReader reader = new BufferedReader(new InputStreamReader(
            new FileInputStream(sqlFile), StandardCharsets.UTF_8))) {
      // 临时变量用于存储每行读取的SQL语句
      String tmp;
      // 循环读取文件的每一行
      while ((tmp = reader.readLine()) != null) {
        // 记录日志，输出当前行内容
        log.info("line:{}", tmp);
        // 判断当前行是否以分号结束，表示一条SQL语句的结束
        if (tmp.endsWith(";")) {
          // 将当前行内容添加到SQL拼接字符串中
          sb.append(tmp);
          // 将完整的SQL语句添加到列表中
          sqlList.add(sb.toString());
          // 清空字符串构建器，为下一条SQL语句做准备
          sb.delete(0, sb.length());
        } else {
          // 如果当前行不是SQL语句的结束，则继续拼接到字符串构建器中
          sb.append(tmp);
        }
      }
      // 检查字符串构建器中是否还有未完成的SQL语句，如果有则添加到列表中
      if (!"".endsWith(sb.toString().trim())) {
        sqlList.add(sb.toString());
      }
    }

    // 返回包含SQL语句的列表
    return sqlList;
  }

}
